package com.neteast.androidclient.newscenter.db;

import java.io.File;
import java.util.ArrayList;
import java.util.Collection;
import com.neteast.androidclient.newscenter.Global;
import com.neteast.androidclient.newscenter.R;
import com.neteast.androidclient.newscenter.domain.Information;
import com.neteast.androidclient.newscenter.util.ImageUtil;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.drawable.Drawable;
import android.util.Log;

public class InfoDao {
	
	private Context context;
	private DbHelper dbHelper;
	private SQLiteDatabase db;
	private Resources resources;
	
	
	public InfoDao(Context context) {
		this.context=context;
		resources=context.getResources();
	}
	
	public void open() {
		dbHelper=new DbHelper(context);
		db = dbHelper.getWritableDatabase();
	}
	
	public void close() {
		dbHelper.close();
	}
	
	
	
	/***************************************查询消息**************************************************************/
	
	/**
	 * 得到所有的系统消息
	 * @return
	 */
	public ArrayList<Information> getSysInfos(int userId){
//		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_SYS_SUFFIX;
//		if (userId==0) {
//			tableName=Global.TABLE_NAME_GUEST;
//		}
		String tableName=Global.TABLE_NAME_GUEST;
		String sql="SELECT * FROM "+tableName;
		return select(sql);
	}
	
	/**
	 * 得到所有的应用消息
	 * @return
	 */
	
	public ArrayList<Information> getAppInfos(int userId){
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_APP_SUFFIX;
		String sql="SELECT * FROM "+tableName;
		return select(sql);
	}
	
	/**
	 * 得到所有的互动消息
	 * @return
	 */
	public ArrayList<Information> getInterActiveInfos(int userId){
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_INTERACTIVE_SUFFIX;
		String sql="SELECT * FROM "+tableName;
		return select(sql);
	}
	
	/**
	 * 查询消息操作
	 * @param sql
	 * @param params
	 * @return
	 */
	private ArrayList<Information> select(String sql){
		Cursor cursor = db.rawQuery(sql, null);
		ArrayList<Information> informations=new ArrayList<Information>();
		while (cursor.moveToNext()) {
				Information information = transfer(cursor);
				informations.add(information);
		}
		cursor.close();
		return informations;
	}
	
	/**
	 * 将cursor指向的一条数据库记录转换为Information
	 * @param cursor
	 * @return
	 */
	private Information transfer(Cursor cursor) {
		Information information=new Information();
		information.infoId = cursor.getLong(cursor.getColumnIndexOrThrow("info_id"));
		information.infoTypeId=cursor.getInt(cursor.getColumnIndexOrThrow("info_type_id"));
		information.appId=cursor.getInt(cursor.getColumnIndexOrThrow("app_id"));
		information.appName=cursor.getString(cursor.getColumnIndexOrThrow("app_name"));
		//如是是应用通知的话，那么应用对应的缩略图不为空，则把infoThumbnail设置为应用的缩略图
		String app_thumbnail_path = cursor.getString(cursor.getColumnIndexOrThrow("app_thumbnail_path"));
		
		information.userId=cursor.getLong(cursor.getColumnIndexOrThrow("user_id"));
		information.userName=cursor.getString(cursor.getColumnIndexOrThrow("user_name"));
		information.interactiveType=cursor.getInt(cursor.getColumnIndexOrThrow("interactive_type"));
		
		//如是消息类型是互动消息的话，那么根据分享的互动信息的类型，给infoThumbnail赋予不同的小图标
		if (information.infoTypeId==Global.INFO_TYPE_INTERACTIVE) {
			switch (information.interactiveType) {
			case Global.INFO_INTERACTIVE_MOVIE:
				information.infoThumbnail=resources.getDrawable(R.drawable.thumbnail_movie);
				information.ineractiveTypeContent=resources.getString(R.string.info_interactive_movie);
				break;
			case Global.INFO_INTERACTIVE_MUSIC:
				information.infoThumbnail=resources.getDrawable(R.drawable.thumbnail_music);
				information.ineractiveTypeContent=resources.getString(R.string.info_interactive_music);
				break;
			case Global.INFO_INTERACTIVE_PICTURE:
				information.infoThumbnail=resources.getDrawable(R.drawable.thumbnail_pic);
				information.ineractiveTypeContent=resources.getString(R.string.info_interactive_picture);
				break;
			case Global.INFO_INTERACTIVE_APP:
				information.infoThumbnail=resources.getDrawable(R.drawable.thumbnail_app);
				information.ineractiveTypeContent=resources.getString(R.string.info_interactive_app);
				break;
			}
		}else if(app_thumbnail_path!=null&&information.interactiveType==Global.INFO_TYPE_APP){
			information.infoThumbnail=Drawable.createFromPath(app_thumbnail_path);
		}
		
		information.sendTime=cursor.getLong(cursor.getColumnIndexOrThrow("send_time"));
		
		//该值不为空，说明消息为带图的系统广播，那么将其权重设置为2
		String sys_pic_path = cursor.getString(cursor.getColumnIndexOrThrow("sys_pic_path"));
		if (sys_pic_path!=null) {
			information.picUrl=sys_pic_path;
			information.weight=2;
		}
		
		information.textContent=cursor.getString(cursor.getColumnIndexOrThrow("textContent"));
		information.goURL=cursor.getString(cursor.getColumnIndexOrThrow("go_url"));
		return information;
	}
	/***************************************存储消息**************************************************************/
	
	/**
	 * 存储消息
	 * @param info
	 * @param userId
	 */
	public void saveInfo(Information info,int userId) {
		
		if (userId==0) {
			saveGuestInfo(info);
		}else {
			switch (info.infoTypeId) {
			case Global.INFO_TYPE_APP:
				saveAppInfo(info, userId);
				break;
			case Global.INFO_TYPE_INTERACTIVE:
				saveInterActiveInfo(info, userId);
				break;
			case Global.INFO_TYPE_SYS:
				saveGuestInfo(info);
				break;
			}
		}
	}
	/**
	 * 存储系统消息
	 * @param info
	 * @param userName
	 */
	private void saveSysInfo(Information info,int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_SYS_SUFFIX;
		insert(info, tableName);
	}
	/**
	 * 存储应用通知
	 * @param info
	 * @param userName
	 */
	private void saveAppInfo(Information info,int userId){
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_APP_SUFFIX;
		insert(info, tableName);
	}
	/**
	 * 存储互动信息
	 * @param info
	 * @param userName
	 */
	private void saveInterActiveInfo(Information info,int userId){
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_INTERACTIVE_SUFFIX;
		insert(info, tableName);
	}
	/**
	 * 存储guest消息
	 * @param info
	 */
	private void saveGuestInfo(Information info){
		String tableName=Global.TABLE_NAME_GUEST;
		insert(info, tableName);
	}
		
	/**
	 * 存储一条information
	 * @param info
	 */
	private void insert(Information info,String tableName){
		if (!isExist(info, db,tableName)) {
			db.execSQL("INSERT INTO "+tableName+"(info_id, info_type_id, app_id, app_name," +
						"app_thumbnail_path, user_id, user_name, interactive_type," +
						"send_time, textContent, go_url, sys_pic_path) " +
						"values(?,?,?,?,?,?,?,?,?,?,?,?)",
						new Object[]{info.infoId,info.infoTypeId,info.appId,info.appName,
						info.infoThumbnailPath,info.userId,info.userName,info.interactiveType,
						info.sendTime,info.textContent,info.goURL,info.picUrl});
			clearTable(db, tableName);
		}
	}
	
	/**
	 * 根据消息的id判断消息是否已经在数据库中存在了
	 * @param information
	 * @param db
	 * @return
	 */
	private boolean isExist(Information information,SQLiteDatabase db,String tableName){
		Cursor rawQuery = db.rawQuery("SELECT * FROM "+tableName+" WHERE info_id=?",new String[]{String.valueOf(information.infoId)});
		int count = rawQuery.getCount();
		rawQuery.close();
		return count>0;
	}
	/**
	 * 清空消息库
	 * @param tableName
	 */
	private void clearTable(SQLiteDatabase db,String tableName){
		Cursor cursor = db.rawQuery("SELECT * FROM "+tableName, null);
		if (cursor.getCount()>21) {
			cursor.close();
			
			String sql="SELECT t1.* FROM "+tableName+" as t1 ,"+tableName+" as t2 WHERE t1.info_id < t2.info_id";
			Cursor rawQuery = db.rawQuery(sql, null);
			rawQuery.moveToFirst();
			Information info = transfer(rawQuery);
			rawQuery.close();
			delete(info, tableName);
			File saveFile = ImageUtil.makeSaveFile(info, context);
			if (saveFile!=null&&saveFile.exists()&&saveFile.isFile()) {
				saveFile.delete();
			}
		}
	}
	/***************************************删除消息**************************************************************/
	/**
	 * 清空当前页
	 * @param infos
	 */
	public void clearCurrentPage(Collection<Information> infos,int userId){
		for (Information information : infos) {
			deleteInfo(information, userId);
		}
	}
	/**
	 * 删除一条消息
	 * @param info
	 * @param i
	 */
	public void deleteInfo(Information info,int userId) {
		if (0==userId) {
			deleteGuestInfo(info);
			return;
		}
		switch (info.infoTypeId) {
		case Global.INFO_TYPE_APP:
			deleteAppInfo(info, userId);
			break;
		case Global.INFO_TYPE_SYS:
			deleteSysInfo(info, userId);
			break;
		case Global.INFO_TYPE_INTERACTIVE:
			deleteInterActiveInfo(info, userId);
			break;
		}
	}
	
	/**
	 * 删除一条系统消息
	 * @param info
	 * @param userName
	 */
	private void deleteSysInfo(Information info,int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_SYS_SUFFIX;
		delete(info, tableName);
	}
	/**
	 * 删除一条应用消息
	 * @param info
	 * @param userName
	 */
	private void deleteAppInfo(Information info,int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_APP_SUFFIX;
		delete(info, tableName);
	}
	/**
	 * 删除一条互动信息
	 * @param info
	 * @param userName
	 */
	private void deleteInterActiveInfo(Information info,int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_INTERACTIVE_SUFFIX;
		delete(info, tableName);
	}
	/**
	 * 删除一条guest信息
	 * @param info
	 * @param userName
	 */
	private void deleteGuestInfo(Information info) {
		String tableName=Global.TABLE_NAME_GUEST;
		delete(info, tableName);
	}
	
	/**
	 * 删除一条消息
	 * @param info
	 */
	private void delete(Information info,String tableName){
		db.execSQL("delete from "+tableName+" where info_id=?",new Object[]{info.infoId});
	}
	
	/***************************************创建表**************************************************************/
	/**
	 * 为用户创建其独享的消息表
	 * @param userId
	 */
	public void createUserTable(int userId) {
		Log.i("test", "create new table for user");
		createAppInfoTable(userId);
		createSysInfoTable(userId);
		createInterActiveInfoTable(userId);
		Log.i("test", "create new table for user finished");
	}
	
	private void createInterActiveInfoTable(int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_INTERACTIVE_SUFFIX;
		db.execSQL(makeSql(tableName));
	}

	private void createSysInfoTable(int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_SYS_SUFFIX;
		db.execSQL(makeSql(tableName));
	}

	private void createAppInfoTable(int userId) {
		String tableName=Global.TABLE_NAME_PREFIX+userId+Global.TABLE_NAME_APP_SUFFIX;
		db.execSQL(makeSql(tableName));
	}
	
	private String makeSql(String tableName){
		String sql="CREATE TABLE IF NOT EXISTS "+tableName+
				"(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"info_id BIGINT, " +
				"info_type_id INTEGER, " +
				"app_id INTEGER, " +
				"app_name TEXT," +
				"app_thumbnail_path TEXT," +
				"user_id BIGINT, " +
				"user_name TEXT," +
				"interactive_type INTEGER," +
				"send_time BIGINT," +
				"textContent TEXT," +
				"go_url TEXT," +
				"sys_pic_path TEXT)";
		return sql;
	}
}
